﻿using log4net;
using SQLDataUploadHelper.daos.bases;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Controls;
using System.Xml.Linq;

namespace SQLDataUploadHelper.daos
{
    /// <summary>
    /// MySql处理
    /// </summary>
    public class SqlHandleDao : DatabaseBaseDao
    {
        private readonly ILog log = LogManager.GetLogger(App.LogRepository.Name, typeof(SqlHandleDao));

        /// <summary>
        /// 获取数据
        /// </summary>
        public DataTable? selectData(string tableName, int pageNum = 1, int pageSize = 100)
        {
            DataTable? dataTable = null;
            if (!open())
            {
                log.Error("数据库打开失败");
                return dataTable;
            }
            //where updateserve = 0
            string sql = string.Format("select * from {0} where updateserve = 0 limit {1} offset {2}",
                tableName, pageSize, (pageNum - 1) * pageSize);
            log.Debug(sql);
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            try
            {
                SqlDataReader sqlDataAdapter = sqlCommand.ExecuteReader();
                dataTable = ConvertDataReaderToDataTable(sqlDataAdapter);
                sqlDataAdapter.Dispose();
                LastErrorMessage = string.Empty;
            }
            catch (Exception ex)
            {
                LastErrorMessage = ex.Message;
                log.Error(ex.ToString());
            }
            return dataTable;
        }


        public bool updateById(string tableName, List<string> ids)
        {
            string sql = string.Format("update {0} set updateserve = 1 where id in ({1})",
                tableName, string.Join(",", ids));
            log.Debug(sql);
            SqlCommand command = new SqlCommand(sql, sqlConnection);
            int result = command.ExecuteNonQuery();
            return result > 0;
        }

        private DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
        {
            DataTable datatable = new DataTable("DataTable");
            DataTable schemaTable = dataReader.GetSchemaTable();
            //动态添加列
            try
            {
                foreach (DataRow myRow in schemaTable.Rows)
                {
                    DataColumn myDataColumn = new DataColumn();
                    myDataColumn.DataType = myRow["DataTypeName"].GetType();
                    myDataColumn.ColumnName = myRow[0].ToString();
                    datatable.Columns.Add(myDataColumn);
                }
                //添加数据
                while (dataReader.Read())
                {
                    DataRow myDataRow = datatable.NewRow();
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        myDataRow[i] = dataReader[i].ToString();
                    }
                    datatable.Rows.Add(myDataRow);
                    myDataRow = null;
                }
                schemaTable = null;
                dataReader.Close();
                return datatable;
            }
            catch (Exception ex)
            {
                throw new Exception("转换出错出错!", ex);
                log.Error(ex.ToString());
            }
        }
    }
}
